import pymysql
# import pymssql
from frame_work.logger import Logger
from frame_work.read_config import global_config

logger = Logger(__name__).get_logger()


class Sql(object):

    def __init__(self, flag=1):
        self.flag = flag
        # self.logger = Logger(logger="Sql").get_logger()
        self.cur = None
        self.conn = None

    def __get_connect(self):
        """
        连接mysql
        :return:
        """
        #conn = pymysql.connect(host=self.host,user=self.user,passwd=self.passwd,db=self.db,charset=self.charset)
        # if self.flag == 2:
        #     self.conn = pymssql.connect(host=self.host,user=self.uname,password=self.pwd,database=self.db,charset=self.charset)
        # else:
        if self.flag == 2:
            host = global_config.get_tfx_database_info('host')
            uname = global_config.get_tfx_database_info('username')
            pwd = global_config.get_tfx_database_info('password')
            port = int(global_config.get_tfx_database_info('port'))
            db = global_config.get_tfx_database_info('database')
            charset = global_config.get_tfx_database_info('charset')
            self.conn = pymysql.connect(
                host=host,
                user=uname,
                passwd=pwd,
                db=db,
                charset=charset,
                port=port,
                cursorclass=pymysql.cursors.DictCursor)
            self.cur = self.conn.cursor()
            return self.cur
        host = global_config.get_database_info('host')
        uname = global_config.get_database_info('username')
        pwd = global_config.get_database_info('password')
        port = global_config.get_database_info('port')
        db = global_config.get_database_info('database')
        charset = global_config.get_database_info('charset')
        self.conn = pymysql.connect(
            host=host,
            user=uname,
            passwd=pwd,
            db=db,
            charset=charset,
            cursorclass=pymysql.cursors.DictCursor)
        self.cur = self.conn.cursor()
        return self.cur

    def exec_query(self, sql, params):
        """
        #数据库的查询，params为元祖形式
        :param sql:
        :param params:
        :return:
        """
        try:
            cur = self.__get_connect()
            cur.execute(sql, params)
            resList = cur.fetchall()
        except Exception as e:
            logger.error("数据库查询出错 %s" % e)
            return
        else:
            return resList
        finally:
            self.cur.close()
            self.conn.close()

    def exec_insert(self, sql, params):
        """
        数据库的insert操作
        :param sql:
        :param params:
        :return:
        """
        self.cur = self.__get_connect()
        try:
            self.cur.execute(sql, params)
            # self.conn.commit()
            self.cur.close()
        except Exception as e:
            logger.error("数据库插入数据出错 %s" % e)
            self.conn.rollback()
            return
        finally:
            self.cur.close()
            self.conn.commit()
            self.conn.close()

    def exec_update(self, sql, params):
        """
        数据库的update操作
        :param sql:
        :param params:
        :return:
        """
        self.cur = self.__get_connect()
        try:
            self.cur.execute(sql, params)
            # self.conn.commit()
        except Exception as e:
            logger.error("数据库更新数据出错 %s" % e)
            self.conn.rollback()
            # return False
        # else:
        #     return True
        finally:
            self.cur.close()
            self.conn.commit()
            self.conn.close()

    def exec_delete(self, sql, params):
        """
        数据库的del操作
        :param sql:
        :param params:
        :return:
        """
        try:
            self.cur = self.__get_connect()
            self.cur.execute(sql, params)
            self.conn.commit()
        except Exception as e:
            logger.error("数据库删除数据出错 %s" % e)
            self.conn.rollback()
            return
        finally:
            self.cur.close()
            self.conn.close()

    # 魔术方法,销毁对象前自动被调用
    # def __del__(self):
    #     if self.cur:
    #         print("sql销毁调用-------------------------------")
    #         self.cur.close()
    #         self.conn.close()

    def get_Connect(self):
        return self.__get_connect()


if __name__ == "__main__":
    sql = Sql()
    # sql_Statements = """SELECT jid from sk_join where trade_no_state =3 and report_state=2 and buyer_uname= %s and tid = %s"""
    # sql_Statements = """select uid from sk_user where uname = %s"""
    sql1 = """update sk_user set uid = 2092415245 where uname = %s"""
    sql2 = """update sk_user set gender = 1 where uname = %s"""
    params = ('user156',)
    status1 = sql.exec_update(sql1, params)
    status2 = sql.exec_update(sql2, params)
    if status1 and status2:
        sql.conn.commit()
    else:
        sql.conn.rollback()
    if sql.conn.open:
        print('open')
    sql.cur.close()
    sql.conn.close()
    if not sql.conn.open:
        print('close')

